package at.spengergasse.dao.derby;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import at.spengergasse.dao.BestellenDao;
import at.spengergasse.dao.exception.DAOException;
import at.spengergasse.dao.exception.NotFoundException;
import at.spengergasse.dao.util.JDBCHelper;
import at.spengergasse.model.Bestellen;

public class BestellenDaoDerbyImpl extends AbstractDAODerbyImpl implements
		BestellenDao
{

	private static final String SQL_INSERT = "INSERT INTO bestellen(b_mod_ts, b_p_id,"
			+ " b_r_id, b_menge, b_position) " + "VALUES ( ?, ?, ?, ?, ?)";

	private static final String SQL_UPDATE = "UPDATE bestellen "
			+ "SET b_mod_ts = ?, b_p_id = ?, b_r_id = ?, "
			+ " b_menge = ?, b_position = ? WHERE b_id = ? "
			+ "AND b_mod_ts = ? ";

	private static final String SQL_DELETE = "DELETE FROM bestellen "
			+ "WHERE b_id = ? " + "  AND b_mod_ts = ? ";

	private static final String SQL_SELECT_BY_ID = "SELECT  b_id, b_mod_ts,  b_p_id,"
			+ " b_r_id, b_menge, b_position "
			+ "FROM bestellen "
			+ "WHERE b_id = ?";

	private static final String SQL_SELECT_ALL = "SELECT  b_id, b_mod_ts,  b_p_id,"
			+ " b_r_id, b_menge, b_position from bestellen";

	private PreparedStatement insertStmt = null;
	private PreparedStatement updateStmt = null;
	private PreparedStatement deleteStmt = null;
	private PreparedStatement selectByIdStmt = null;
	private PreparedStatement selectAllStmt = null;

	@Override
	public int delete(Connection connection, Bestellen bestellen)
	{
		validateConnection(connection);
		validateBestellen(bestellen);

		int recordsAffected = 0;

		try
		{
			if (deleteStmt == null)
			{
				deleteStmt = connection.prepareStatement(SQL_DELETE);
			}

			deleteStmt.setLong(1, bestellen.getId());
			deleteStmt.setTimestamp(2, JDBCHelper.toSqlTimestamp(bestellen
					.getLastModifiedTS()));

			recordsAffected = deleteStmt.executeUpdate();
		} catch (SQLException e)
		{
			throw new DAOException("Order could not be deleted!", e);
		}
		return recordsAffected;
	}

	@Override
	public List<Bestellen> findAll(Connection connection)
	{
		validateConnection(connection);

		List<Bestellen> foundBestellen = new ArrayList<Bestellen>();
		Bestellen foundBestellung = null;
		ResultSet rs = null;

		try
		{
			if (selectAllStmt == null)
			{
				selectAllStmt = connection.prepareStatement(SQL_SELECT_ALL);
			}

			rs = selectAllStmt.executeQuery();

			while (rs.next())
			{
				foundBestellung = new Bestellen(rs.getLong(1), rs.getLong(3),
						rs.getLong(4), rs.getInt(5), rs.getInt(6));
				foundBestellung.setLastModifiedTS(JDBCHelper.toDate(rs
						.getTimestamp(2)));
				foundBestellen.add(foundBestellung);
			}
		} catch (SQLException e)
		{
			throw new NotFoundException("Customer could not be read!", e);
		} finally
		{
			closeResultSet(rs);
		}

		return foundBestellen;
	}

	@Override
	public Bestellen findById(Connection connection, Long id)
	{
		validateConnection(connection);
		if (id == null)
			throw new IllegalArgumentException("Id darf nicht null sein!");

		Bestellen foundBestellung = null;
		ResultSet rs = null;
		try
		{
			if (selectByIdStmt == null)
			{
				selectByIdStmt = connection.prepareStatement(SQL_SELECT_BY_ID);
			}

			selectByIdStmt.setLong(1, id);
			rs = selectByIdStmt.executeQuery();

			while (rs.next())
			{
				foundBestellung = new Bestellen(rs.getLong(1), rs.getLong(3),
						rs.getLong(4), rs.getInt(5), rs.getInt(6));
				foundBestellung.setLastModifiedTS(JDBCHelper.toDate(rs
						.getTimestamp(2)));
			}
		} catch (SQLException e)
		{
			throw new NotFoundException("Order could not be found!", e);
		} finally
		{
			closeResultSet(rs);
		}

		return foundBestellung;
	}

	@Override
	public int save(Connection connection, Bestellen bestellen)
	{
		validateConnection(connection);
		validateBestellen(bestellen);
		return (bestellen.isNew()) ? insert(connection, bestellen) : update(
				connection, bestellen);
	}

	private int update(Connection connection, Bestellen bestellen)
	{
		validateConnection(connection);
		validateBestellen(bestellen);

		int recordsAffected = 0;
		Date now = new Date();

		try
		{
			if (updateStmt == null)
			{
				updateStmt = connection.prepareStatement(SQL_UPDATE);
			}

			updateStmt.setTimestamp(1, JDBCHelper.toSqlTimestamp(now));
			updateStmt.setLong(2, bestellen.getProduktId());
			updateStmt.setLong(3, bestellen.getRechnungsId());
			updateStmt.setInt(4, bestellen.getMenge());
			updateStmt.setInt(5, bestellen.getPosition());
			updateStmt.setLong(6, bestellen.getId());
			updateStmt.setTimestamp(7, JDBCHelper.toSqlTimestamp(bestellen
					.getLastModifiedTS()));

			recordsAffected = updateStmt.executeUpdate();

			bestellen.setLastModifiedTS(now);
		} catch (SQLException e)
		{
			throw new DAOException("Customer could not be updated!", e);
		}
		return recordsAffected;
	}

	private int insert(Connection connection, Bestellen bestellen)
	{
		validateConnection(connection);
		validateBestellen(bestellen);

		int recordsAffected = 0;
		Date now = new Date();
		ResultSet generatedKeys = null;

		try
		{
			if (insertStmt == null)
			{
				insertStmt = connection.prepareStatement(SQL_INSERT,
						PreparedStatement.RETURN_GENERATED_KEYS);
			}

			insertStmt.setTimestamp(1, JDBCHelper.toSqlTimestamp(now));
			insertStmt.setLong(2, bestellen.getProduktId());
			insertStmt.setLong(3, bestellen.getRechnungsId());
			insertStmt.setInt(4, bestellen.getMenge());
			insertStmt.setInt(5, bestellen.getPosition());
			recordsAffected = insertStmt.executeUpdate();
			generatedKeys = insertStmt.getGeneratedKeys();

			if (generatedKeys.next())
			{
				long currentId = generatedKeys.getLong(1);
				bestellen.setId(currentId);
				bestellen.setLastModifiedTS(now);
			}
		} catch (SQLException e)
		{
			throw new DAOException("Order could not be inserted!", e);
		} finally
		{
			closeResultSet(generatedKeys);
		}
		return recordsAffected;
	}

	private void validateBestellen(Bestellen bestellen)
	{
		if (bestellen == null)
		{
			throw new IllegalArgumentException("Zu speichernde Bestellung darf"
					+ "nicht null sein");
		}

	}

}
